if exists (select 1
            from  sysobjects
           where  id = object_id('bd_view_timecard_dto')
            and   type = 'V')
   drop view bd_view_timecard_dto
go

/*==============================================================*/
/* View: bd_view_timecard_dto                                   */
/*==============================================================*/
create view bd_view_timecard_dto as
SELECT
 a.id,
 a.user_serial AS userSerial,
 user_no AS workNo,
 user_name AS staffName,
 dept_id AS deptId,
 dept_name AS department,
 CONVERT(VARCHAR, card_date) + ' ' + card_week AS dateWeek,
 ISNULL(CONVERT(VARCHAR(10), card_date, 120), '') AS date,
 card_week AS week,
 shift_name AS ShiftTyle,
 CASE
   WHEN dbo.bd_fn_numToMin3(shift_begin) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin3(shift_begin)
 END AS workStart,
 CASE
   WHEN dbo.bd_fn_numToMin3(shift_end) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin3(shift_end)
 END AS workEnd,
 CASE
   WHEN dbo.bd_fn_numToMin3(swipe_begin) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin3(swipe_begin)
 END AS inClock,
 CASE
   WHEN dbo.bd_fn_numToMin3(swipe_end) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin3(swipe_end)
 END AS outClock,
 status AS attendanceStatus,
 CASE
   WHEN dbo.bd_fn_numToMin(should_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin(should_hours)
 END AS shouldHours,
 CASE
   WHEN dbo.bd_fn_numToMin(actual_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin(actual_hours)
 END AS realOutHours,
 total_hours AS cumulativeHours,
 shift_middle_count AS zhongBanTimes,
 shift_night_count AS yeBanTimes,
 pending_hours AS Pending2,
 CASE
   WHEN dbo.bd_fn_numToMin(pending_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin(pending_hours)
 END AS Pending,
 ot_type_id AS jiabanId,
 ISNULL(ot_type_name, '') AS jiabanTyle,
 CASE
   WHEN dbo.bd_fn_numToMin2(ot_begin) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin2(ot_begin)
 END AS startTime,
 CASE
   WHEN dbo.bd_fn_numToMin2(ot_end) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin2(ot_end)
 END AS endTime,
 CASE
   WHEN dbo.bd_fn_numToMin2(ot_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin2(ot_hours)
 END AS applyHours,
 CASE
   WHEN ot_is_eating = 1 THEN
     '是'
   ELSE
     ''
 END AS isDining,
 ot_pending_hours AS daiQueRen2,
 CASE
   WHEN dbo.bd_fn_numToMin(ot_pending_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin(ot_pending_hours)
 END AS daiQueRen,
 CASE
   WHEN dbo.bd_fn_numToMin(ot_normal_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin(ot_normal_hours)
 END AS UsuallyJiaban,
 CASE
   WHEN dbo.bd_fn_numToMin(ot_weekend_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin(ot_weekend_hours)
 END AS WeekendJiaban,
 CASE
   WHEN dbo.bd_fn_numToMin(ot_holiday_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin(ot_holiday_hours)
 END AS HolidaysJiaban,
 CASE
   WHEN dbo.bd_fn_numToMin(ot_to_rest_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin(ot_to_rest_hours)
 END AS overtimeBreak,
 CASE
   WHEN dbo.bd_fn_numToMin(total_hours) = '00:00' THEN
     ''
   ELSE
     dbo.bd_fn_numToMin(total_hours)
 END AS totalWork,
 ISNULL(leave_type, '') AS leaveTyle,
 leave_hours AS leaveTime,
 CASE
   WHEN CONVERT(VARCHAR, ot_bill_id) != 0 THEN
     CONVERT(VARCHAR, ot_bill_id)
   ELSE
     ''
 END AS otBillId,
 ISNULL(CONVERT(VARCHAR(19), sign_begin, 120), '') AS signBegin,
 ISNULL(CONVERT(VARCHAR(19), sign_end, 120), '') AS signEnd,
 CASE
   WHEN CONVERT(VARCHAR, leave_bill_id) != 0 THEN
     CONVERT(VARCHAR, leave_bill_id)
   ELSE
     ''
 END AS leaveBillId,
 is_swipe AS sSwipe,
 ot_bill_status AS otBillStatus,
 swipe_begin_repair AS swipeBeginRepair,
 swipe_end_repair AS swipeEndRepair,
 isnull(b.lock_status,0) AS lockStatus,
 a.should_hours-a.actual_hours-a.leave_hours_total as leaveMinutes
FROM bd_kq_timecard a
left join bd_kq_accounts b on a.user_serial = b.user_serial and YEAR(a.card_date) = b.year and  month(a.card_date) = b.month
go
